<?php
class DbReaderTest extends PHPUnit_Framework_TestCase {

    private $db = null;


    const TEST_DB            = 'mysql://ucms736:bafamkWDLDtl3hN0@localhost/world';
    const PATH_DBSIMLE       = '/opt/http/cms736/trunk/library/dbsimple/DbSimple/lib/DbSimple/Generic.php';
    const PATH_DBSIMLE_MYSQL = '/opt/http/cms736/trunk/library/dbsimple/DbSimple/lib/DbSimple/Mysql.php';
    const EXEC_DROP_DB       = 'echo \'DROP DATABASE IF EXISTS world;\' | mysql -uucms736 -pbafamkWDLDtl3hN0';
    const EXEC_CREATE_DB     = 'echo \'CREATE DATABASE world;\' | mysql -uucms736 -pbafamkWDLDtl3hN0';
    const EXEC_DUMP_LOAD     = 'zcat /opt/http/cms736/trunk/t/resources/world.sql.gz | mysql -uucms736 -pbafamkWDLDtl3hN0 world';


    // Код обработчика ошибок SQL.
    public function databaseErrorHandler ($message, $info) {
        throw new Exception($message.' ('.$info.')');
    }


    /**
     * @return DbSimple_Generic_Database
     */
    private function getDb () {
        if (is_null($this->db)) {
            require_once self::PATH_DBSIMLE;
            require_once self::PATH_DBSIMLE_MYSQL;
            //exec(self::EXEC_DROP_DB);
            //exec(self::EXEC_CREATE_DB);
            //exec(self::EXEC_DUMP_LOAD);
            $this->db = DbSimple_Generic::connect(self::TEST_DB);
            // Устанавливаем обработчик ошибок.
            $this->getDb()->setErrorHandler(array($this, 'databaseErrorHandler'));
        }
        return $this->db;
    }


    /**
     * @param string $table
     * @param string $key
     * @return DB_Reader
     */
    private function _createDbReader ($table, $key) {
        return new DB_Reader($this->getDb(), $table, $key);
    }

    public function testWorkingPrepareTest () {
        $db = $this->getDb();
        $arExpectsTables = array(
            array('Tables_in_world' => 'City'),
            array('Tables_in_world' => 'Country'),
            array('Tables_in_world' => 'CountryLanguage')
        );
        $this->assertEquals($arExpectsTables,$db->select('SHOW TABLES'));
    }

    const TEST_TABLE = 'City';
    const TEST_KEY   = 'ID';

    public function testConstruct () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals(self::TEST_TABLE, $objDbReader->getSource());
        $this->assertEquals(self::TEST_KEY, $objDbReader->getKey());
        $this->assertEquals($this->getDb(), $objDbReader->getDb());
    }


    public function testElement_getResult () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult =  array(
            'ID' => 10,
            'Name' => 'Tilburg',
            'CountryCode' => 'NLD',
            'District' => 'Noord-Brabant',
            'Population' => 193238
        );

        $this->assertEquals($arResult, $objDbReader->element(10));
    }


    public function testElements_getResultByList () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->elements(10, 20, 30);
        $this->assertEquals(3, count($arResult));
        $this->assertEquals(10, $arResult[0][self::TEST_KEY]);
        $this->assertEquals(20, $arResult[1][self::TEST_KEY]);
        $this->assertEquals(30, $arResult[2][self::TEST_KEY]);
    }


    public function testElements_getResultByArray () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->elements(array(10, 20, 30));

        $this->assertEquals(3, count($arResult));
        $this->assertEquals(10, $arResult[0][self::TEST_KEY]);
        $this->assertEquals(20, $arResult[1][self::TEST_KEY]);
        $this->assertEquals(30, $arResult[2][self::TEST_KEY]);
    }


    public function testMiniSql_FullQuery () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arMiniSql = array(
            'Population < ? | Population ASC',
            10000
        );

        $arResult = $objDbReader->msql($arMiniSql[0], $arMiniSql[1]);

        $this->assertEquals(42, count($arResult));
    }


    public function testMiniSql_OrderByQuery () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arMiniSql = array(
            '|Population DESC'
        );

        $arResult = $objDbReader->msql($arMiniSql[0]);

        $this->assertEquals('Mumbai (Bombay)', $arResult[0]['Name']);
    }


    public function testMiniSql_WhereQuery () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arMiniSql = array(
            'Name LIKE ?',
            'X%'
        );

        $arResult = $objDbReader->msql($arMiniSql[0], $arMiniSql[1]);

        $this->assertEquals(24, count($arResult));
    }


    public function testLimit_MiniSqlQuery () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arMiniSql = array(
            'Name LIKE ?',
            'X%'
        );

        $arResult = $objDbReader->limit(1 ,$arMiniSql[0], $arMiniSql[1]);

        $this->assertEquals(1, count($arResult));
        $this->assertEquals('Shaanxi', $arResult[0]['District']);
    }


    public function testCount_WhithWhereQuery () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arWhere = array(
            'Name LIKE ?',
            'X%'
        );

        $iResult = $objDbReader->count($arWhere[0], $arWhere[1]);

        $this->assertEquals(24, $iResult);
    }


    public function testCount_AllQuery () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $iResult = $objDbReader->count();

        $this->assertEquals(4079, $iResult);
    }


    public function testNumberOfPages_GetAllPages () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $iResult = $objDbReader->numberOfPages(100);

        $this->assertEquals(41, $iResult);
    }


    public function testNumberOfPages_GetPagesWithWhere () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $iResult = $objDbReader->numberOfPages(10, '1 {AND `Name` LIKE ?}', 'Y%');

        $this->assertEquals(7, $iResult);
    }


    public function testPage_GetPageAll () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 10;
        $arResult = $objDbReader->page($page, 100);

        $this->assertEquals(10, $page);
        $this->assertEquals(100, count($arResult));
        $this->assertEquals(901, $arResult[0]['ID']);
        $this->assertEquals(1000, $arResult[99]['ID']);
    }


    public function testPage_GetPageWithWhere () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 2;
        $arResult = $objDbReader->page($page, 5, 'Name LIKE ? | Name ASC', 'A%');

        $this->assertEquals(2, $page);
        $this->assertEquals(5, count($arResult));
        $this->assertEquals(395, $arResult[0]['ID']);
        $this->assertEquals(478, $arResult[4]['ID']);
    }


    public function testPage_ManyPages () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 100;
        $arResult = $objDbReader->page($page, 30, 'Name LIKE ? | Name ASC', 'A%');


        $this->assertEquals(9, $page);
    }


    public function testIsExists_True () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals(true, $objDbReader->isExists(100));
    }


    public function testIsExists_False () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals(false, $objDbReader->isExists(5000));
    }


    public function testIsExistsByWhere_True () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals(true, $objDbReader->isExistsByWhere('ID = ?', 100));
    }


    public function testIsExistsByWhere_False () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals(false, $objDbReader->isExistsByWhere('ID = ?', 5000));
    }


    public function testValue_getExistsValue () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals('Tilburg', $objDbReader->value(10, 'Name'));
    }


    public function testValue_getNotExistsValue () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals('', $objDbReader->value(5000, 'Name'));
    }


    public function testValueBy_getExistsValue () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals('Amsterdam', $objDbReader->valueBy('Name', 'ID < ?| Name ASC', 11));
    }


    public function testValueBy_getNotExistsValue () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $this->assertEquals('', $objDbReader->valueBy('Name', 'ID > ?| Name ASC', 5000));
    }


    public function testColumn_getColumn () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->column('Name', 4, 'ID < ?| Name ASC', 10);
        $this->assertEquals(4, count($arResult));
        $this->assertEquals('Amsterdam', $arResult[5]);
        $this->assertEquals('Herat', $arResult[3]);
    }


    public function testColumn_getAllColumn () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->column('Name', '', 'ID <= ?| Name ASC', 10);
        $this->assertEquals(10, count($arResult));
    }


    public function testColumns_getColumns () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->columns(array('Name','Population'), 4, 'ID < ?| Name ASC', 10);

        $this->assertEquals(4, count($arResult));
        $this->assertEquals('Amsterdam', $arResult[5]['Name']);
        $this->assertEquals('Herat', $arResult[3]['Name']);
    }


    public function testElementBy_getResult () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->elementBy('ID < ?| Name ASC', 10);

        $this->assertEquals(5, count($arResult));
        $this->assertEquals('Amsterdam', $arResult['Name']);
        $this->assertEquals(5, $arResult['ID']);
    }


    /**
     * @expectedException Exception
     */
    public function testElementBy_setEmptyRequest () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->elementBy();
    }


    public function testElementBy_emptyResult () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $arResult = $objDbReader->elementBy('ID > ?| Name ASC', 5000);

        $this->assertTrue(empty($arResult));
    }


    public function testPageByFilterArray_getPageIfNotUseOrderAndNotUseFilter () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 2;
        $arResult = $objDbReader->pageByFilterArray($page, 10);

        $this->assertEquals(10, count($arResult));
        $this->assertEquals(172701, $arResult[0]['Population']);
        $this->assertEquals(129170, $arResult[9]['Population']);
    }


    public function testPageByFilterArray_getPageWithUseOrderAndNotUseFilter () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 2;
        $arResult = $objDbReader->pageByFilterArray($page, 10, 'Population');

        $this->assertEquals(10, count($arResult));
        $this->assertEquals(961, $arResult[0]['Population']);
        $this->assertEquals(2000, $arResult[9]['Population']);
    }


    public function testPageByFilterArray_getPageWithIfNotUseOrderAndUseFilter () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 2;
        $arResult = $objDbReader->pageByFilterArray($page, 10, '', array('Population' => 'equal|961'));


        $this->assertEquals(1, count($arResult));
        $this->assertEquals(961, $arResult[0]['Population']);
    }


    public function testPageByFilterArray_getPageWithUseOrderAndUseFilter () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $page = 2;
        $arResult = $objDbReader->pageByFilterArray($page, 10, 'Population', array('Population' => 'less|1500'));

        $this->assertEquals(6, count($arResult));
        $this->assertEquals(1438, $arResult[5]['Population']);
    }


    public function testNumberOfPagesByFilterArray_NotUseFilter () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $iResult = $objDbReader->numberOfPagesByFilterArray('100');

        $this->assertEquals(41, $iResult);
    }


    public function testNumberOfPagesByFilterArray_UseFilter () {
        $objDbReader = $this->_createDbReader(self::TEST_TABLE, self::TEST_KEY);

        $iResult = $objDbReader->numberOfPagesByFilterArray('100', array('Population' => 'more|150000'));

        $this->assertEquals(23, $iResult);
    }

}